The Business Master (3rd Edition)

home *** CD-ROM | disk | FTP | other *** search

/ The Business Master (3rd Edition) / The Business Master (3rd Edition).iso / files / spreeets / freeeasy / fe.exe / CHPT3.TXT < prev next >

Wrap

Text File | 1992-03-07 | 21.0 KB | 505 lines

page 3-1 CHAPTER 3: CELLS, VARIABLES AND FUNCTIONS This chapter will outline in detail how FREE & EASY does calculations. Thus we will look at how cells and variables are used and how they work together. All features looked at in this chapter are found in the "Cell" pull-down menu. How Calculations Are Done FREE & EASY does all its calculations in cells. If you understand how a spreadsheet uses cells, then you have a good idea of how cells work in FREE & EASY. In a spreadsheet, you are locked into a grid of rows and columns of cells. With FREE & EASY, however, the situation is different. FREE & EASY has a sheet onto which you add text wherever you wish. And on the sheet you can also add cells wherever you need them. This allows you to add descriptive text in the vicinity of the cells to describe what calculations your cells are doing. Just as a spreadsheet's cells have contents in the form of some mathematical expression, the cells of FREE & EASY have contents in the form of a mathematical expression. The cell contents can contain numbers, variables, the value of another cell and several types of functions. When the contents of the cell are evaluated, the cell then has a value. This value is displayed on the sheet. For each cell, the format in which that value is displayed can be modified to suit the situation. With a spreadsheet, a cell's contents can refer to the value of a variable by name, but that variable can only be another cell. In FREE & EASY, a variable can be defined anywhere in the sheet with an "=" sign. For example, suppose in the text of the sheet, the following is found: distance = 4.36 The variable "distance" can now be used in any of the cell contents, and when FREE & EASY encounters the variable "distance" in the cell contents when calculating the cell value, it will substitute the value 4.36. Like a spreadsheet, any cell in FREE & EASY can be given a name, and that name can be used as a variable by any other cell. In this case, when FREE & EASY encounters the cell name when evaluating the cell contents it will substitute the value of the cell. In the cell contents, FREE & EASY also allows you to refer to the value at a location in the sheet. In this case, there can be a number at this location or another cell. FREE & EASY will substitute this value when evaluating the cell. There are also functions (called range functions) in FREE & EASY which use a range (a user defined rectangular area of the sheet) and do calculations on all values found in that range. As you can see, these features give you tremendous flexibility to construct sheets of considerable complexity. Yet you also have the page 3-2 ability to add cells where you need them, to add descriptive text, and to add variables where desired. This allows you to keep your sheet easy to follow and understand. Adding and Removing Cell(s) to the Sheet There are several ways to add cells to the sheet. A single cell can be added with the "Add/modify cell ...", "cell Contents ..." or "cell Name ..." menu items. The cell is added at the present cursor position. If the cursor is already on a cell when one these menu items is called, that cell will be modified rather than a new cell added. You can remove a cell from the sheet by placing the cursor on the cell you wish to remove and selecting the "Remove cell" menu item. (control-R is the short cut key for this). Blocks of cells can also be moved, copied and deleted. This is not covered in this chapter but in chapter 4 on Editing. The cells on the sheet are highlighted. Several cells at once can be added to the sheet with the "Table of cells ..." menu item. Cell Contents When you enter the cell contents, if you are adding a new cell to the sheet, the initial cell contents that you will see are those of the default cell. The default cell is used extensively by FREE & EASY and more is said on it later. If you are modifying a cell, then the initial cell contents you will see are the present cell contents of the cell that you are modifying. The cell contents can be any valid mathematical expression up to 200 characters long. It is unlikely, however, that you will ever have cell contents this long. It is often better to break up a complicated expression into several cells. When you enter the cell contents, FREE & EASY removes all spaces, converts functions to upper case, and checks the syntax. If there is an error in the syntax, FREE & EASY displays a message indicating what the error is and will not accept what you have typed. Remember, if you get an error message, you can always press "F1" to get further explanation. Appendix B has a full listing of syntax errors. When evaluating cell contents, FREE & EASY follows the rules of algebra, i.e. the cell contents are evaluated left to right with the following preference of operators: 2^3 raise to power =8 2*3 multiply =6 2/3 divide =1.5 2+3 add =5 2-3 subtract =-1 When you enter the cell contents, FREE & EASY can also give you a warning. This usually means that even though the syntax is correct, page 3-3 there can be an error when evaluating the cell contents. If this happens, you should check the what cell contents you have typed in to make sure that you have entered what you intended. Appendix C has a complete list of warnings. Brackets ("(" and ")") can be used to change the precedent of operator evaluations. You cannot use the curly brackets or the square brackets. Of course, the brackets must be used algebraically correctly or a syntax error will be given. FREE & EASY has several types of functions which can be used. All functions have three letters and are upper case. Remember that functions are converted to upper case when you enter them. Appendix A lists all functions available, and chapter 6 covers functions in greater detail. The argument of a function must be in brackets. i.e. SIN(30) is acceptable but SIN 30 is not acceptable. The cell contents can also contain variables. If two variables are defined in the sheet as follows: length=4 width=3 then if the cell contents were "length*width" the cell value upon evaluation would be 12. The contents of a cell can refer to a location on the sheet. There must be a valid number or cell at that location; otherwise an evaluation error will be given for that cell. If you want to refer to the sheet location at row 15 and column 126 (note that the right side of the prompt bar shows the cursor location in row:column format) then the following found in the cell contents would show that location: !0015!0126 Note that the row and column locations are each preceded by the '!' character and each are four digits. (Preceding 0's are added if necessary) If this syntax is not followed exactly, a syntax error will be given when you are trying to enter the cell contents. The location referred to is relative to the cell containing the location. This means that if the cell is moved (with one of the editing features for example) the location referred to moves correspondingly. The same applies to entering cell contents for the default cell. It is possible to make the location referred to absolute by using the '$' character instead of '!'. Thus for the example above, if the location is to be absolute, the following would appear in the cell contents: $0015$0126 In this case if the cell containing this location is moved the location does not change. Of course, if the cell is never moved it makes no difference whether the location referred to is relative or absolute. It is also possible to have the row location absolute and the column page 3-4 location relative (or vice versa). For example: $0015!0126 In this case if the cell is moved the row referred to remains the same and the column changes. There are several sample sheets included in the FREE & EASY package where examples of this are used. It is also possible to refer to a location on the sheet other than the sheet in which the cell occurs. For example: &FILE1.FRE$0015$0126 In this case reference is made to row 15 and column 126 on the sheet named FILE1.FRE. The '&' character must precede the file name. For a location on another sheet, the location must be absolute. If the location is entered as relative FREE & EASY will change it to absolute. When entering cell contents and you want to input a sheet location, the location can be typed in as described above. However, an easier way is to press the '\' key while typing in the cell contents. At this point the menu will disappear. The cursor control keys are used to bring the cursor to the location you wish to refer to in the cell contents. The F5 and F6 keys can be used to refer to a location in another sheet. Press Enter to accept that location as relative, press control-Enter to accept that location as absolute or press Escape to cancel. You will come back to the menu you just left. FREE & EASY will have added the location you have chosen (if you did not press Escape) in the correct format to the cell contents. If you press the '\' key while the cursor in the cell contents is on a valid sheet location, then the cursor will start out at that location. Otherwise it will start out at the location of the cell whose contents you are entering. When entering the cell contents, you can use the key combination control-P to enter the symbol for PI. When evaluating a cell and this symbol is encountered, the value 3.14159... is used. After a cell has been successfully evaluated, then the cell's value is that evaluated value. The cell value is then displayed on the sheet in the format for that cell. If there is an evaluation error, the cell value is set to 0 and this value is displayed. Variables Variables are defined on the sheet with an equal "=" sign. The variable name is to the left and variable value to the right of the equal sign. Any number of spaces may be between the equal sign and name and value. All must be on the same line. Once the variable has been defined, it can be used in the contents of any cell. You should give variables meaningful names as this will make your calculations easier to follow. page 3-5 When you enter the cell contents and use a variable name, FREE & EASY checks to ensure that you have used a valid name. The name must start with a letter and contain only letters and numbers or the underscore character (no spaces allowed). Names are case sensitive, i.e.. FREE & EASY treats var1, VAR1 and Var1 all as different variables. The maximum length is 20 characters. If an invalid name is used, an error message is given. When evaluating a cell and FREE & EASY encounters a variable name, it finds the variable on the sheet then looks for its defined value. This value must be a valid number. The number must be between 1E300, -1E300, 1E-300 and -1E-300, and must have the correct syntax. For example, the number 30g6 is not an acceptable number. If the number is not acceptable, FREE & EASY will give an error message. The same rules for a number assigned to a variable also apply to numbers in the cell contents. Cell Name Any cell can be given a name. The cell name must start with a letter. Then any combination of letters or digits or the underscore character can follow. The maximum length is 20 characters. The cell name can be used to refer to the value of that cell in the contents of another cell. Both a cell and a variable can have the same name (although this is not recommended). When a cell is being evaluated and a name is encountered, it can be a cell name or a variable name. FREE & EASY will search until it finds a value (either cell or variable) for that name. Whether it starts searching for a cell name or variable name first depends on the evaluation defaults. These are discussed in chapter 6. The cell name can be adjusted on the "Add/modify cell" menu item or the "cell Name" menu item. Format FREE & EASY allows you to use a variety of formats to display the value of the cell on the sheet. When a new cell is added to the sheet, the format in the default cell is used. This can be changed, however, with the "Add/modify ..." menu item. At the bottom of this menu, the cell is shown as it will appear on the sheet. You can use this to adjust the format to give the appearance you want before you add or modify the cell. The following format items can be adjusted: size: This is the size of the cell on the sheet. The cell can be from 1 to 21 characters wide. decimals: This is the number of decimal places the display has. It can be 0 to 14. FREE & EASY keeps track of 14 significant figures. style: There are four choices here: floating point: This is probably the one you will use most often; page 3-6 exponential: This is scientific notation and is useful for very large or very small numbers; integer: This display shows no decimal point or decimal places; free: For this style, FREE & EASY makes the choice of floating point or exponential depending on the value of the cell. justify: The display value can be justified at the left or right of the cell. plus sign: A leading plus sign can be added to the number if it is positive. If the number is negative, a leading minus sign will always be added. zeros: Leading zeros can be added to fill up the entire cell space. The format chosen in no way affects the value of the cell. It is possible that the cell value and what is displayed in the cell are not the same. For example, the cell value could be 2.4678. If the decimal is set to two, the display will be 2.47. When you are using a cell name or location of a cell in the cell contents, FREE & EASY uses the actual value of the cell and not the value displayed in its calculations. It is also possible that the various format items you set are not compatible. For example, if size is set to 10, but the decimal is set 11, the display value cannot fit into the cell. In this case, the cell display is filled with "*'s". Again the cell value is not affected. Default Cell FREE & EASY has a default cell which is used in many situations. We have already seen it used in adding cells to the sheet if the cell contents or format are not adjusted. The settings in the default cell can be changed with the "set cell Defaults ..." menu item. There is no default cell name. This is because FREE & EASY does not allow two cells in the same sheet to have the same name. With this exception, the items in "set cell Defaults ..." are adjusted the same way as in the "Add/modify cell ..." menu. Table of Cells There are times when you might want to add a block of cells to the sheet at one time. It would be very tedious to add them one at a time, so FREE & EASY allows you to add a block of cells at one time using the "table of Cells" menu item. Before adding the cells, you can adjust the number of rows and columns of cells to add as well as the spacing between the rows and columns. The top left corner of the block of cells will be at the present cursor position. If the table of cells runs off the bottom or edge of the sheet, an error message is given and the cells will not be added. As well, page 3-7 before the cells are added all text and cells within the area of the sheet where the cells will appear are removed. Even cells which straddle the border of the block of cells will be removed. if text or cells must be removed in order to add the table of cells, FREE & EASY will give a "data will be lost" warning asking for confirmation that you wish to continue. The contents and format of the cells added will be that of the default cell. Thus before adding a table of cells, ensure that the default cell has the contents and format you want for your table of cells. Table of Data As well as adding a table of cells, you can also add a table of data (or numbers) to the sheet with the "taBle of data" menu item. As the "Table of cells" menu, you can adjust the rows, columns and spacing. Again FREE & EASY checks that the table will fit on the sheet, erases any text and cells necessary to make room for the table and warns you if this will be done. You can also adjust the initial value for the table (the value of the number in top left corner) as well as the increment between the values. Both of these must be valid numbers. FREE & EASY will tell you if they are not. The format used to add the table of data is the format of the default cell. Before adding the table of data, ensure that the format is what you want. It is possible if the format is not what you want that you will not get the number you want in the table. For example, if the initial value is 1.00 and the increment value is 0.01, and the default cell has decimal set to 1, the first three entries in the table of data will be 1.0, 1.0 and 1.0. (and not 1.00, 1.01 and 1.02) Sheet Locations in Cell Contents You have already seen how you can refer to a location on the sheet in the cell contents. The location is referred to by a row and column number. You can refer to a cell or number at this location. (FREE & EASY will check for both when evaluating the cell.) Remember that the location referred to can be relative or absolute. If the location is relative and you move the cell, this location changes in the same way that you moved the cell. (e.g. If the cell moves down 2 and right 3, this location moves down 2 and right 3 as well.) The contents of the default cell also behave like this. You can move the cursor around and call up the default cell and see how a sheet location changes (if the location is relative). If you add a table of cells and the default contents include a sheet location which is relative, this location will be different for every cell in the table. If you want to refer to a specific cell or specific location on the sheet regardless of how a cell is moved you can use an absolute address or refer to that cell by its name (and give it a name if it does not have one.) You may also assign a variable to a number at that location and use the variable name for that number in the cell contents. page 3-8 Remove All Cells The "rEmove all cells" menu item will erase every cell on the sheet. This can be useful if you want to start from scratch on adding cells to a sheet. FREE & EASY asks you to confirm that you really want to do this before proceeding. Format Highlighted Area Sometimes you might want to change the format of a group of cells. The format of one cell can be changed with the "Add/modify cell" menu item but if there are several cells to change, this can be tedious. Thus a whole group of cells can have their format changed with the "Format highlighted area" menu item. To use this menu item, first you must make sure the default cell has the format you want. You then highlight the area of the sheet containing the cells where you want to change the format. To do this, use the "Highlight" menu item in the "Edit" pull-down menu. (You can also start this with control-H.) More details on highlighting are given in chapter 4. You can also simply follow the prompt bar to use the highlighting features. Once the area has been highlighted, choose the "Format highlighted area" menu item. All cells completely within the highlighted area will have their format changed to the format of the default cell. There are two situations where it is possible to lose data in doing this procedure. If you are increasing the size of the cell and it will overlap text, that text will be erased, or if it will overlap another cell, that cell will be removed (cells cannot overlap). If by increasing the size of a cell, you force the cell off the edge of the sheet, that cell will be removed. If any of these situations occur when formatting a highlighted area, FREE & EASY will give a "data will be lost" warning and ask for confirmation about whether you want to proceed.